<?php
/**
 * Created by JetBrains PhpStorm.
 * User: Spionatiev Ivan
 * Date: 10/12/12
 * Time: 5:21 PM
 * To change this template use File | Settings | File Templates.
 */

class Db_Products_Poems extends Common {
	protected static $tableName = TAB_POEMS;
	protected static $dbFields = array(
		'id',
		'pe_title',
		'pe_content',
		'pe_author',
		'pe_show_on_page',
		'pe_project_identifier',
		'deleted'
	);

	/**
	 * @var Db_Products_Poems
	 */
	public $objectArray;

	public $id;
	public $pe_title;
	public $pe_content;
	public $pe_author;
	public $pe_show_on_page;
	public $pe_project_identifier;
	public $deleted;

	function __construct($value=NULL, $fieldName=NULL) {
		parent::__construct($value, $fieldName);
	}

	public static function getTotalPoems($sqlCondition = '1=1') {
		if (empty($sqlCondition)) {
			$sqlCondition = '1=1';
			$joinSearch = '';
		} else {
			$joinSearch = " LEFT JOIN " . TAB_TRANSLATIONS . " tr ON tr.t_module = pe.pe_module AND tr.t_key = pe.pe_title AND tr.t_lang_id = " . conf('COUNTRY_LANG_ID') ;
			$joinSearch .= " LEFT JOIN " . TAB_TRANSLATIONS . " tr2 ON tr2.t_module = pe.pe_module AND tr2.t_key = pe.pe_content AND tr2.t_lang_id = " . conf('COUNTRY_LANG_ID') ;
			$joinSearch .= " LEFT JOIN " . TAB_TRANSLATIONS . " tr3 ON tr3.t_module = pe.pe_module AND tr3.t_key = pe.pe_author AND tr3.t_lang_id = " . conf('COUNTRY_LANG_ID') ;

			$sqlCondition .= " OR (`tr`.`t_content` LIKE " . self::$DB->escapeValue('%' . str_replace('%', "\\%", post('sSearch')) . '%') . ")";
			$sqlCondition .= " OR (`tr2`.`t_content` LIKE " . self::$DB->escapeValue('%' . str_replace('%', "\\%", post('sSearch')) . '%') . ")";
			$sqlCondition .= " OR (`tr3`.`t_content` LIKE " . self::$DB->escapeValue('%' . str_replace('%', "\\%", post('sSearch')) . '%') . ")";
		}

		$sql = "SELECT COUNT(DISTINCT `pe`.`id`) FROM ?f pe {$joinSearch} WHERE `pe`.`deleted` = 0 AND {$sqlCondition}";
		return self::$DB->selectCell($sql, self::$tableName);
	}

	public static function getAllPoems($limit, $searchSql = '', $orderBy = '') {
		if (empty($searchSql)) {
			$searchSql = '1=1';
			$joinSearch = '';
		} else {
			$joinSearch = " LEFT JOIN " . TAB_TRANSLATIONS . " tr ON tr.t_module = pe.pe_module AND tr.t_key = pe.pe_title AND tr.t_lang_id = " . conf('COUNTRY_LANG_ID') ;
			$joinSearch .= " LEFT JOIN " . TAB_TRANSLATIONS . " tr2 ON tr2.t_module = pe.pe_module AND tr2.t_key = pe.pe_content AND tr2.t_lang_id = " . conf('COUNTRY_LANG_ID') ;
			$joinSearch .= " LEFT JOIN " . TAB_TRANSLATIONS . " tr3 ON tr3.t_module = pe.pe_module AND tr3.t_key = pe.pe_author AND tr3.t_lang_id = " . conf('COUNTRY_LANG_ID') ;

			$searchSql .= " OR (`tr`.`t_content` LIKE " . self::$DB->escapeValue('%' . str_replace('%', "\\%", post('sSearch')) . '%') . ")";
			$searchSql .= " OR (`tr2`.`t_content` LIKE " . self::$DB->escapeValue('%' . str_replace('%', "\\%", post('sSearch')) . '%') . ")";
			$searchSql .= " OR (`tr3`.`t_content` LIKE " . self::$DB->escapeValue('%' . str_replace('%', "\\%", post('sSearch')) . '%') . ")";
		}
		if (empty($orderBy)) {
			$orderBy = ' ORDER BY `pe`.`id` ASC ';
		}
		$sql = "SELECT `pe`.*, `pps`.`pps_product_id` as `productId`  FROM ?f pe  {$joinSearch} LEFT JOIN ?f pps ON `pe`.`id` = `pps`.`pps_poem_id`  WHERE `pe`.`deleted` = 0 AND {$searchSql} GROUP BY `pe`.`id` {$orderBy} {$limit}";
		return self::$DB->select($sql, self::$tableName, TAB_PRODUCTS_POEMS);
	}

	public static function getAllPoemIds() {
		return self::$DB->selectCol('SELECT id FROM ?f', self::$tableName);
	}

	public static function countPoemsByProjectIdentifier($identifiers) {
		$sql = 'SELECT COUNT(*) AS `counts`
				FROM ?f WHERE `deleted` = 0 AND `pe_project_identifier` IN( ? )
				GROUP BY `pe_project_identifier` ORDER BY `counts` DESC LIMIT 1';
		return self::$DB->selectCell($sql, self::$tableName, $identifiers);
	}

	public static function getPoemsByProductId($productId) {
		if (!$productId) {
			return false;
		}
		$sql = "SELECT pe.*,pe.id as poem_id FROM ?f pe
				INNER JOIN ?f pps ON pe.id = pps.pps_poem_id
				WHERE pps.pps_product_id = ?d AND pe.deleted = 0 AND pe.pe_project_identifier = ?
				";
		return self::$DB->select($sql, self::$tableName, TAB_PRODUCTS_POEMS, $productId, self::$PROJECT['pr_identifier']);
	}


}